package com.tydic.springboot.util;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigInteger;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;

/**
 * @author: wangkl
 * @date: 2020-03-10 17:18
 * @description:
 */
public class HSSFWorkbookUtil {

    public static Workbook getWorkBook(MultipartFile file) {
        String filename = file.getOriginalFilename();
        Workbook workbook = null;
        try (InputStream ins = file.getInputStream()) {
            if (filename.toLowerCase().endsWith("xls")) {
                workbook = new HSSFWorkbook(ins);
            } else if (filename.toLowerCase().endsWith("xlsx")) {
                workbook = new XSSFWorkbook(ins);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return workbook;
    }

    public static Workbook getWorkBook(File file) {
        String filename = file.getName();
        Workbook workbook = null;
        try (InputStream ins = new FileInputStream(file)) {
            if (filename.toLowerCase().endsWith("xls")) {
                workbook = new HSSFWorkbook(ins);
                System.out.println("2003及以下");
            } else if (filename.toLowerCase().endsWith("xlsx")) {
                workbook = new XSSFWorkbook(ins);
                System.out.println("2007及以上");
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return workbook;
    }

    /**
     * 描述：对表格中数字或字符串格式化
     */
    public static Object getCellValue(Cell cell) {
        Object v_cell = null;
        if (cell != null){
            switch (cell.getCellType()) {
                case NUMERIC:
                    if(HSSFDateUtil.isCellDateFormatted(cell)){
                        //日期型
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");  //日期格式化
                        v_cell = sdf.format(cell.getDateCellValue());
                    }else{
                        v_cell = getRealStringValueOfDouble(cell.getNumericCellValue());
                    }
                    break;
                case STRING:
                    v_cell = cell.getRichStringCellValue().getString().trim();
                    break;
                case FORMULA://公式
                    break;
                case BLANK://空
                    v_cell = null;
                    break;
                case BOOLEAN:
                    v_cell = cell.getBooleanCellValue();
                    break;
                case ERROR:
                    break;
                default:
                    break;
            }
        }else{
            v_cell = null;
        }
        return v_cell;
    }

    public static String getRealStringValueOfDouble(Double d) {
        String doubleStr = d.toString();
        boolean b = doubleStr.contains("E");
        int indexOfPoint = doubleStr.indexOf('.');
        if (b) {
            int indexOfE = doubleStr.indexOf('E');
            BigInteger xs = new BigInteger(doubleStr.substring(indexOfPoint
                    + BigInteger.ONE.intValue(), indexOfE));
            int pow = Integer.valueOf(doubleStr.substring(indexOfE
                    + BigInteger.ONE.intValue()));
            int xsLen = xs.toByteArray().length;
            int scale = xsLen - pow > 0 ? xsLen - pow : 0;
            doubleStr = String.format("%." + scale + "f", d);
        } else {
            Pattern p = Pattern.compile(".0$");
            java.util.regex.Matcher m = p.matcher(doubleStr);
            if (m.find()) {
                doubleStr = doubleStr.replace(".0", "");
            }
        }
        return doubleStr;
    }

    public static void main(String[] args) {
//        File file = new File("C:\\Users\\Administrator\\Desktop\\测试.xlsx");
        File file = new File("C:\\Users\\Administrator\\Desktop\\DEC__表6 干休所安置人员情况统计表2.xls");
        Workbook workBook = getWorkBook(file);
        Sheet sheet = workBook.getSheetAt(1);

//        List<Integer> intList = mergedRowList(sheet, 0, 0);
//        for (Integer index:intList) {
//            System.out.println("第"+ index +"行是合并单元格");
//        }

        int lastRowNum = sheet.getLastRowNum();
        System.out.println(DateUtil.dateFormatyyyyMMddHHmmss());
        for (int i = 0; i <= lastRowNum; i++) {
            Map<String, Object> result = isMergedRegion(sheet, i, 0);
            if((boolean)result.get("result")){
                System.out.println("第"+ i +"行是合并单元格");
                System.out.println(result);
                int rowMergeNum = Integer.parseInt(result.get("rowMergeNum").toString());
            }
        }
        System.out.println(DateUtil.dateFormatyyyyMMddHHmmss());
    }

    /**
     * 判断指定的单元格是否是合并单元格
     * @param sheet
     * @param row 行下标
     * @param column 列下标
     * @return
     */
    public static Map<String, Object> isMergedRegion(Sheet sheet, int row, int column) {
        Map<String, Object> map = new HashMap<String, Object>();
        boolean flag = false;//判断结果
        int row_index = -1;//当前行
        int colum_index = -1;//当前列

        map.put("row_index", row);
        map.put("colum_index", column);
        // 得到一个sheet中有多少个合并单元格
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            // 获取合并后的单元格
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow //判断行
                    && column >= firstColumn && column <= lastColumn) {//判断列
                flag = true;
                map.put("firstRow", firstRow);
                map.put("lastRow", lastRow);
                map.put("firstColumn", firstColumn);
                map.put("lastColumn", lastColumn);
                map.put("rowMergeNum", lastRow - firstRow);
                map.put("columnMergeNum", lastColumn - firstColumn);
                break;
            }
        }
        map.put("result", flag);
        return map;
    }

    /**
     *
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    public static List<Integer> mergedRowList(Sheet sheet, int row, int column) {
        List<Integer> intList = new ArrayList<>();
        // 得到一个sheet中有多少个合并单元格
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            // 获取合并后的单元格
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if(firstColumn == column){
                intList.add(firstRow);
            }
        }
        return intList;
    }

    /**
     * 判断指定的行是否是合并行以及最大行值
     *
     * @param sheet
     * @param row  行下标
     * @param column 列下标
     * @return
     */
    public Map<String, String> isMergedRow(Sheet sheet, int row, int column) {
        Map<String, String> map = new HashMap<String, String>();
        //判断结果
        String flag = "false";
        //最大row
        String mr = row + "";
        map.put("Results", flag);
        map.put("MaxRow", mr);
        // 得到一个sheet中有多少个合并单元格
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            // 获取合并后的单元格
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    flag = "true";
                    map.put("Results", flag);
                    map.put("MaxRow", lastRow + "");
                    return map;
                }
            }
        }
        return map;
    }
}
